IF OBJECT_ID(N'dbo.GetCOIActivitiesByCOIIdAndOrgName', N'FN') IS NOT NULL
BEGIN
DROP FUNCTION dbo.GetCOIActivitiesByCOIIdAndOrgName
END
GO
/****** Object:  UserDefinedFunction [dbo].[GetCOIActivitiesByCOIIdAndOrgName]    Script Date: 09/16/2009 12:39:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************
*  Name:  GetCOIActivitiesByCOIIdAndOrgName
* Purpose:  Function to get the COI in a seperated list.
	This function is called from GetDisclosuresByCOIOrgAdmPersonID stored Proc.
*
* PARAMETERS
* Name    			Description					
* -------------  	-------------------------------------------
* @COIId            ConflictOfInterest ID	
* @COIOrgName		ConflictOfInterest Oragnization Name
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------- 
* FundName
******************************************************************/

CREATE FUNCTION [dbo].[GetCOIActivitiesByCOIIdAndOrgName](
	@COIId INTEGER,
	@COIOrgName VARCHAR(100)
)
	RETURNS VARCHAR(200)
AS
BEGIN

	DECLARE @returnValue VARCHAR(200)
	SET @returnValue = ''
	
	SELECT 
    @returnValue = @returnValue + ', ' +
		CASE  
			WHEN q.[Name] = 'Your Outside Activities' THEN 'Outside Activities'  
			WHEN q.[Name] IN ('Your Equity Interests','Trustee Association') THEN 'Equity Interests'  
			WHEN q.[Name] IN ('Your Family''s Associations and Equity Interests','Trustee''s Family Associations and Equity Interests') THEN 'Family Associations'  
			WHEN q.[Name] = 'Your Intellectual Property' THEN 'Royalty' 
		END
	FROM 
	QuestionnaireAnswer qa 
	INNER JOIN coi_questionnaireanswer_map coiqmap  on coiqmap.questionnaireAnswerId = qa.id 
	INNER JOIN ConflictOfInterest coi on coi.id = coiqmap.coiid 
	INNER JOIN admPerson adp on adp.id = coi.admPersonId 
	INNER JOIN QuestionnaireVersion qv on qv.id = qa.QuestionnaireVersionId 
	INNER JOIN Questionnaire q on q.id = qv.QuestionnaireId 
	INNER JOIN COIOrganization coiOrg on coiOrg.Id = qa.ExternalId 
	WHERE 
	ISNULL(CAST(coi.[Id] as varchar(10)),'*') =(
												SELECT ISNULL(CAST(MAX(Id)AS VARCHAR(10)),'*') AS LatestCOIId FROM conflictofinterest 
												WHERE coi.admPersonId = AdmPersonId AND Status <> 'COISTATUS_TERMINATED'
												)  
	AND  (coiOrg.Reviewed = 0 OR (coiOrg.Reviewed = 1 AND coiOrg.Active = 1)) 
	AND  coi.Id = @COIId
	AND  qa.[Name] = @COIOrgName

	SELECT @returnValue = SUBSTRING(@returnValue, 2, 200)

	RETURN @returnValue

END




